package com.example.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.entity.TempC;
import com.example.mapper.TempCMapper;
import com.example.service.ITempCService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author 王鹤蒲
 * @since 2023-08-17
 */
@Service
public class TempCServiceImpl extends ServiceImpl<TempCMapper, TempC> implements ITempCService {

    @Autowired
    private TempCMapper tempCMapper;

    @Override
    public void importExcel(MultipartFile file) throws IOException {
        Workbook workbook = null;
        if (!file.isEmpty()) {
            workbook=new XSSFWorkbook(file.getInputStream());;
            Sheet sheet = workbook.getSheetAt(0);
            int rowNum = sheet.getPhysicalNumberOfRows();
            List<TempC> tempCList = new ArrayList<>();
            for (int i = 0; i < rowNum - 1; i++) {
                TempC tempC = new TempC();
                Row row = sheet.getRow(i + 1);
                Cell cell1 = row.getCell(0);
                Cell cell2 = row.getCell(1);
                Cell cell3 = row.getCell(2);
                Cell cell4 = row.getCell(3);
                Cell cell5 = row.getCell(4);
                Cell cell6 = row.getCell(5);
                Cell cell7 = row.getCell(6);
                Cell cell8 = row.getCell(7);
                Cell cell9 = row.getCell(8);
                Cell cell10 = row.getCell(9);
                Cell cell11 = row.getCell(10);
                Cell cell12 = row.getCell(11);
                Cell cell13 = row.getCell(12);
                Cell cell14 = row.getCell(13);
                Cell cell15 = row.getCell(14);
                Cell cell16 = row.getCell(15);
                Cell cell17 = row.getCell(16);
                Cell cell18 = row.getCell(17);
                Cell cell19 = row.getCell(18);
                Cell cell20 = row.getCell(19);
                Cell cell21 = row.getCell(20);
                Cell cell22 = row.getCell(21);
                Cell cell23 = row.getCell(22);
                Cell cell24 = row.getCell(23);
                Cell cell25 = row.getCell(24);
                Cell cell26 = row.getCell(25);
                Cell cell27 = row.getCell(26);
                Cell cell28 = row.getCell(27);
                Cell cell29 = row.getCell(28);
                Cell cell30 = row.getCell(29);
                Cell cell31 = row.getCell(30);
                Cell cell32 = row.getCell(31);
                Cell cell33 = row.getCell(32);
                Cell cell34 = row.getCell(33);
                Cell cell35 = row.getCell(34);
                if (cell1 != null) {
                   tempC.setA(cell1.getStringCellValue());
                }
                if (cell2 != null) {
                    tempC.setB(cell2.getStringCellValue());
               }
                if (cell3 != null) {
                    tempC.setC(cell3.getStringCellValue());
                }
                if (cell4 != null) {
                    tempC.setD(cell4.getStringCellValue());
                }
                if (cell5 != null) {
                    tempC.setE(cell5.getStringCellValue());
                }
                if (cell6 != null) {
                    tempC.setF(cell6.getStringCellValue());
                }
                if (cell7 != null) {
                    tempC.setG(cell7.getStringCellValue());
                }
                if (cell8 != null) {
                    tempC.setH(cell8.getStringCellValue());
                }
                if (cell9 != null) {
                    tempC.setI(cell9.getStringCellValue());
                }
                if (cell10 != null) {
                    tempC.setJ(cell10.getStringCellValue());
                }
                if (cell11 != null) {
                    tempC.setK(cell11.getStringCellValue());
                }
                if (cell12 != null) {
                    tempC.setL(cell12.getStringCellValue());
                } if (cell13 != null) {
                    tempC.setM(cell13.getStringCellValue());
                }
                if (cell14 != null) {
                    tempC.setN(cell14.getStringCellValue());
                }
                if (cell15 != null) {
                    tempC.setO(cell15.getStringCellValue());
                }
                if (cell16 != null) {
                    tempC.setP(cell16.getStringCellValue());
                }
                if (cell17 != null) {
                    if("设备告警".equals(cell17.getStringCellValue())) {
                        tempC.setQ("设备原始告警");
                    }else if("性能告警".equals(cell17.getStringCellValue())){
                        tempC.setQ("实时性能告警");
                    }else{
                        tempC.setQ(cell17.getStringCellValue());
                    }
                }
                if (cell18 != null) {
                    tempC.setR(cell18.getStringCellValue());
                }
                if (cell19 != null) {
                    tempC.setS(cell19.getStringCellValue());
                }
                if (cell20 != null) {
                    tempC.setT(cell20.getStringCellValue());
                }
                if (cell21 != null) {
                    tempC.setU(cell21.getStringCellValue());
                }
                if (cell22 != null) {
                    tempC.setV(cell22.getStringCellValue());
                } if (cell23 != null) {
                    tempC.setW(cell23.getStringCellValue());
                }
                if (cell24 != null) {
                    tempC.setX(cell24.getStringCellValue());
                }
                if (cell25 != null) {
                    tempC.setY(cell25.getStringCellValue());
                }
                if (cell26 != null) {
                    tempC.setZ(cell26.getStringCellValue());
                }
                if (cell27 != null) {
                    tempC.setAa(cell27.getStringCellValue());
                }
                if (cell28 != null) {
                    tempC.setAb(cell28.getStringCellValue());
                }
                if (cell29 != null) {
                    tempC.setAc(cell29.getStringCellValue());
                }
                if (cell30 != null) {
                    tempC.setAd("虚拟化");
                }
                if (cell31 != null) {
                    tempC.setAe(cell31.getStringCellValue());
                }
                if (cell32 != null) {
                    tempC.setAf(cell32.getStringCellValue());
                }
                if (cell33 != null) {
                    tempC.setAg(cell33.getStringCellValue());
                }
                if (cell34 != null) {
                    tempC.setAh(cell34.getStringCellValue());
                }
                if (cell35 != null) {
                    tempC.setAi(cell35.getStringCellValue());
                }
                tempCList.add(tempC);
            }
            for ( TempC tempC:tempCList){
                tempCMapper.insert(tempC);
            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<String> selectDList() {
        return tempCMapper.selectDList();
    }

    @Override
    public List<String> selectEList() {
        return tempCMapper.selectEList();
    }

    @Override
    public List<String> selectQList() {
        return tempCMapper.selectQList();
    }

    @Override
    public List<TempC> selectSplitTempCList(String d, String e, String q) {
        return tempCMapper.selectSplitTempCList(d,e,q);
    }

    @Override
    public void export() throws FileNotFoundException {
     List<String> dList=selectDList();
     List<String> eList=selectEList();
     List<String> qList=selectQList();
     for(int i=0;i<dList.size();i++){
         for(int j=0;j<eList.size();j++){
             for(int k=0;k<qList.size();k++) {
                 String[] headers = {"映射规则序号", "映射方式", "厂家", "设备类型", "告警对象类型", "告警标题", "厂家告警ID", "告警解释辅助字段", "厂家告警级别", "适用的厂家版本号", "告警处理级别", "网管告警级别", "14位网管告警ID", "网管告警ID", "告警解释", "告警类型", "告警逻辑分类", "告警逻辑子类", "该事件对设备的影响", "该事件对业务的影响", "告警标准名", "是否上报集团", "设备单板", "速率相关性", "层速率", "告警检测点", "专业", "业务类型", "业务信息", "是否与对端网元相关", "梳理表名"};
                 System.out.println(dList.get(i) + eList.get(j) + qList.get(k));
                 List<TempC> tempCList = tempCMapper.selectSplitTempCList(dList.get(i), eList.get(j), qList.get(k));
                 if(tempCList.size()>0){
                     XSSFWorkbook workbook = new XSSFWorkbook();
                     XSSFSheet xssfSheet = workbook.createSheet();
                     XSSFRow xssfRow=xssfSheet.createRow(0);
                    String[] AHs=tempCList.get(0).getAi().split(",");
                     for(int h=0;h<headers.length;h++){
                        String header=headers[h];
                         for(int o=0;o<AHs.length;o++){
                             if(header.equals(AHs[o])){
                                 header+="(key)";
                             }
                         }
                       XSSFCell xssfCell= xssfRow.createCell(h);
                       xssfCell.setCellValue(header);
                     }
                 for (int l = 0; l < tempCList.size(); l++) {
                     TempC tempC = tempCList.get(l);
                     XSSFRow xssfRow1 = xssfSheet.createRow(l + 1);
                     XSSFCell cell1 = xssfRow1.createCell(0);
                     cell1.setCellValue(tempC.getA());
                     XSSFCell cell2 = xssfRow1.createCell(1);
                     cell2.setCellValue(tempC.getB());
                     XSSFCell cell3 = xssfRow1.createCell(2);
                     cell3.setCellValue(tempC.getD());
                     XSSFCell cell4 = xssfRow1.createCell(3);
                     cell4.setCellValue(tempC.getE());
                     XSSFCell cell5 = xssfRow1.createCell(4);
                     cell5.setCellValue(tempC.getF());
                     XSSFCell cell6 = xssfRow1.createCell(5);
                     cell6.setCellValue(tempC.getG());
                     XSSFCell cell7 = xssfRow1.createCell(6);
                     cell7.setCellValue(tempC.getH());
                     XSSFCell cell8 = xssfRow1.createCell(7);
                     cell8.setCellValue(tempC.getI());
                     XSSFCell cell9 = xssfRow1.createCell(8);
                     cell9.setCellValue(tempC.getJ());
                     XSSFCell cell10 = xssfRow1.createCell(9);
                     cell10.setCellValue(tempC.getK());
                     XSSFCell cell11 = xssfRow1.createCell(10);
                     cell11.setCellValue(tempC.getL());
                     XSSFCell cell12 = xssfRow1.createCell(11);
                     cell12.setCellValue(tempC.getM());
                     XSSFCell cell13 = xssfRow1.createCell(12);
                     cell13.setCellValue(tempC.getN());
                     XSSFCell cell14 = xssfRow1.createCell(13);
                     cell14.setCellValue(tempC.getO());
                     XSSFCell cell15 = xssfRow1.createCell(14);
                     cell15.setCellValue(tempC.getP());
                     XSSFCell cell16 = xssfRow1.createCell(15);
                     cell16.setCellValue(tempC.getQ());
                     XSSFCell cell17 = xssfRow1.createCell(16);
                     cell17.setCellValue(tempC.getR());
                     XSSFCell cell18 = xssfRow1.createCell(17);
                     cell18.setCellValue(tempC.getS());
                     XSSFCell cell19 = xssfRow1.createCell(18);
                     cell19.setCellValue(tempC.getT());
                     XSSFCell cell20 = xssfRow1.createCell(19);
                     cell20.setCellValue(tempC.getU());
                     XSSFCell cell21 = xssfRow1.createCell(20);
                     cell21.setCellValue(tempC.getV());
                     XSSFCell cell22 = xssfRow1.createCell(21);
                     cell22.setCellValue(tempC.getW());
                     XSSFCell cell23 = xssfRow1.createCell(22);
                     cell23.setCellValue(tempC.getX());
                     XSSFCell cell24 = xssfRow1.createCell(23);
                     cell24.setCellValue(tempC.getY());
                     XSSFCell cell25 = xssfRow1.createCell(24);
                     cell25.setCellValue(tempC.getZ());
                     XSSFCell cell26 = xssfRow1.createCell(25);
                     cell26.setCellValue(tempC.getAa());
                     XSSFCell cell27 = xssfRow1.createCell(26);
                     cell27.setCellValue(tempC.getAd());
                     XSSFCell cell28 = xssfRow1.createCell(27);
                     cell28.setCellValue(tempC.getAe());
                     XSSFCell cell29 = xssfRow1.createCell(28);
                     cell29.setCellValue(tempC.getAf());
                     XSSFCell cell30 = xssfRow1.createCell(29);
                     cell30.setCellValue(tempC.getAg());
                     XSSFCell cell31 = xssfRow1.createCell(30);
                     cell31.setCellValue(tempC.getAh());
                  }
                 FileOutputStream fileOutputStream = new FileOutputStream("D:\\upload\\NFV\\" + "NFV_"+dList.get(i) + eList.get(j) + qList.get(k) + ".xls");
                 try {
                     workbook.write(fileOutputStream);
                     fileOutputStream.close();
                 } catch (IOException e) {
                     throw new RuntimeException(e);
                 }
             }
             }
         }
     }
     }
}
